package test;

import java.io.*;
import java.sql.*;
import java.util.*;

/**
	@author jkhsieh
	
	Text-based interface to see what's in tables.
	Insert, update, delete functionality are on wish list.
*/
public class MySqlConsole
{
   private String connectionURL = "jdbc:mysql://localhost:3306/mojito?user=root;password=mojito";
   private Connection connection = null;
   private Statement statement = null;
   private ResultSet rs = null;
   private ResultSetMetaData rsMeta;
   private int cols;
   
   private String select = "SELECT * FROM mojito.";
   private String insert = "INSERT INTO mojito.";    //VALUES( , )    
   private String update = "UPDATE mojito.";         //SET _=_,_=_ WHERE _=_
   private String delete = "DELETE FROM mojito.";    //WHERE _=_
   
   private String table = null;
   
   
   public MySqlConsole() throws InstantiationException, IllegalAccessException, 
   ClassNotFoundException, SQLException
   {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      connection = DriverManager.getConnection(connectionURL, "root", "mojito");
      statement = connection.createStatement();
   }
   
   
   /** print menu */
   public void menu(String table)
   {
      System.out.println("\n-------------MySQL Mojito DB--------------");
      System.out.println("Use [u]sers table");
      System.out.println("Use [g]roups table");
      System.out.println("Use [e]vents table");
      
      if(table!=null)
      {
         System.out.println("Do [i]nsert into " + table + "...");
         System.out.println("Do [u]pdate " + table + "...");
         System.out.println("Do [d]elete from " + table + "...");
      }
      
      System.out.println("[q]uit");
   }
   
   
   public void showTable(String table) throws SQLException
   {
      //query
      this.table = table;
      rs = statement.executeQuery(select + table);
      
      //get headers...
      rsMeta = rs.getMetaData();
      cols = rsMeta.getColumnCount();
      
      //data from query
      while(rs.next())
      {
         System.out.print("\t");
         for(int i=1; i<=cols; i++)
         {
            System.out.print(rs.getString(i));
            if(i!=cols)
               System.out.print(", ");
         }
         System.out.println();
      }
   }//showTable
   
   
   public void insert(String table) throws SQLException
   {
      //get field count and types
      //fields' java classes
      System.out.print("Field classes: ");
      for(int i=1; i<=cols; i++)
      {
         System.out.print(rsMeta.getColumnClassName(i));
         if(i!=cols)
            System.out.print(", ");
      }
      System.out.println();

      //column names
      System.out.print("["+ rsMeta.getTableName(1) + "] table fields: ");
      for(int i=1; i<=cols; i++)
      {
         System.out.print(rsMeta.getColumnName(i));
         if(i!=cols)
            System.out.print(", ");
      }
      System.out.println();
      
      
      //prompt for input for each field
      Scanner in = new Scanner(System.in);
      String input = "";
      if(in.hasNextLine())
         input = in.nextLine();
      
      //update db
   }
   
   
   /*
         //methods
         //insert, update, or delete?
         System.out.print("[i]nsert into, [u]pdate, or [d]elete from " +rsMeta.getTableName(1)+ "?: ");
    */
   
   
   public static void main(String[] args) throws InstantiationException, 
   IllegalAccessException, ClassNotFoundException, SQLException
   {
      Scanner scanner = new Scanner(System.in);
      String line = "";
      
      MySqlConsole ms = new MySqlConsole();
      
      //print menu
      ms.menu(ms.table);
      
      //get input, route cases
   w: while(scanner.hasNextLine())
      {
         line = scanner.next();
         char ch = line.charAt(0);
         
         switch(ch)
         {
            case 'u':   ms.showTable("users");   break;
            case 'g':   ms.showTable("groups");  break;
            case 'e':   ms.showTable("events");  break;
            
            case 'i':   ms.insert(ms.table);        break;
            case 'p':   break;
            case 'd':   break;
            
            case 'q':   break w;
            default :   System.out.println("Try again");
         }
         
         ms.menu(ms.table);
         
      }//while scanner
      
   }//main

}//console class
